﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DComplaint
    {
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperMySQL.GetMaxID("id", "tcomplaint");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tcomplaint");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.ComplaintInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tcomplaint(");
            strSql.Append("Complaintsobject,Complaintreason,ComplaintAid,ComplaintBid,time)");
            strSql.Append(" values (");
            strSql.Append("@Complaintsobject,@Complaintreason,@ComplaintAid,@ComplaintBid,@time)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Complaintsobject", MySqlDbType.Int32,10),
					new MySqlParameter("@Complaintreason", MySqlDbType.Text),
					new MySqlParameter("@ComplaintAid", MySqlDbType.VarChar,15),
					new MySqlParameter("@ComplaintBid", MySqlDbType.VarChar,15),
					new MySqlParameter("@time", MySqlDbType.DateTime)};
            parameters[0].Value = model.Complaintsobject;
            parameters[1].Value = model.Complaintreason;
            parameters[2].Value = model.ComplaintAid;
            parameters[3].Value = model.ComplaintBid;
            parameters[4].Value = model.time;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.ComplaintInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tcomplaint set ");
            strSql.Append("Complaintsobject=@Complaintsobject,");
            strSql.Append("Complaintreason=@Complaintreason,");
            strSql.Append("ComplaintAid=@ComplaintAid,");
            strSql.Append("ComplaintBid=@ComplaintBid,");
            strSql.Append("time=@time");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Complaintsobject", MySqlDbType.Int32,10),
					new MySqlParameter("@Complaintreason", MySqlDbType.Text),
					new MySqlParameter("@ComplaintAid", MySqlDbType.VarChar,15),
					new MySqlParameter("@ComplaintBid", MySqlDbType.VarChar,15),
					new MySqlParameter("@time", MySqlDbType.DateTime),
					new MySqlParameter("@id", MySqlDbType.Int32,10)};
            parameters[0].Value = model.Complaintsobject;
            parameters[1].Value = model.Complaintreason;
            parameters[2].Value = model.ComplaintAid;
            parameters[3].Value = model.ComplaintBid;
            parameters[4].Value = model.time;
            parameters[5].Value = model.id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tcomplaint ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tcomplaint ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.ComplaintInfo GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,Complaintsobject,Complaintreason,ComplaintAid,ComplaintBid,time from tcomplaint ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            Model.ComplaintInfo model = new Model.ComplaintInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Complaintsobject"] != null && ds.Tables[0].Rows[0]["Complaintsobject"].ToString() != "")
                {
                    model.Complaintsobject = int.Parse(ds.Tables[0].Rows[0]["Complaintsobject"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Complaintreason"] != null && ds.Tables[0].Rows[0]["Complaintreason"].ToString() != "")
                {
                    model.Complaintreason = ds.Tables[0].Rows[0]["Complaintreason"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ComplaintAid"] != null && ds.Tables[0].Rows[0]["ComplaintAid"].ToString() != "")
                {
                    model.ComplaintAid = ds.Tables[0].Rows[0]["ComplaintAid"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ComplaintBid"] != null && ds.Tables[0].Rows[0]["ComplaintBid"].ToString() != "")
                {
                    model.ComplaintBid = ds.Tables[0].Rows[0]["ComplaintBid"].ToString();
                }
                if (ds.Tables[0].Rows[0]["time"] != null && ds.Tables[0].Rows[0]["time"].ToString() != "")
                {
                    model.time = DateTime.Parse(ds.Tables[0].Rows[0]["time"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表--投诉司机
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select id,Complaintsobject,Complaintreason,ComplaintAid,ComplaintBid,time,Name ");
            //strSql.Append(" FROM vTaxiComplaint ");
            //if (strWhere.Trim() != "")
            //{
            //    strSql.Append(" where " + strWhere);
            //}
            //return DbHelperMySQL.Query(strSql.ToString());
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.id,Complaintsobject,");
            strSql.Append("Complaintreason,ComplaintAid,");
            strSql.Append("ComplaintBid,`time`,tdriver.Name,");
            strSql.Append("ttaxi.TaxiNo,tcompany.CompanyName ");         
            strSql.Append("from tcomplaint t ");
            strSql.Append("left join tdriver on ");
            strSql.Append("t.ComplaintBid=tdriver.PhoneNumber ");
            strSql.Append("left join ttaxi on tdriver.TaxiId = ttaxi.Id ");
            strSql.Append("left join tcompany on ttaxi.CompanyId = tcompany.Id ");            
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());

        }

        /// <summary>
        /// 获得数据列表--投诉乘客
        /// </summary>
        public DataSet GetListOfPasseager(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,Complaintsobject,Complaintreason,ComplaintAid,ComplaintBid,time ");
            strSql.Append(" FROM tcomplaint ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得乘客黑名单列表  ComplaintAid 为乘客  B为司机  0表示投诉  1表示被投诉
        /// </summary>
        public List<string> GetBlackListOfPasseager()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct ComplaintAid ");
            strSql.Append(" FROM tcomplaint where Complaintsobject=1 ");
            strSql.Append("and TO_DAYS(NOW()) - TO_DAYS(time) <=30");            
            DataSet ds =  DbHelperMySQL.Query(strSql.ToString());
            List<string> blackList = new List<string>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    blackList.Add(row[0].ToString());
                }
            }
            return blackList;
        }

        /// <summary>
        /// 获取当月被投诉次数   A表示乘客 B表示司机  object 0表示投诉 1表示被投诉
        /// </summary>
        public int GetRecordCountOfUserInThisMonth(string phoneNumber, int complaintObject)
        {
            DateTime now = DateTime.Now;
            DateTime firstDay = new DateTime(now.Year, now.Month, 1);
            DateTime lastDay = firstDay.AddMonths(1).AddDays(-1);
            //string startDay = DateTime.Today.Date.ToShortDateString();
            //startDay = startDay.Remove(startDay.LastIndexOf('-')+1) + "01";
            //string endDay = startDay.Remove(startDay.Length - 2) + daysInMonth;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) FROM tcomplaint where ");
            strSql.Append("Complaintsobject =");
            strSql.Append(complaintObject);
            //strSql.Append(" and ComplaintBid = '");
            if (complaintObject==0)
            {
                strSql.Append(" and ComplaintBid");
            }
            else 
            {
                strSql.Append(" and ComplaintAid");
            }             
            strSql.Append("= '");            
            strSql.Append(phoneNumber);
            strSql.Append("' and time between '");
            strSql.Append(firstDay.ToShortDateString());
            strSql.Append("' and '");
            strSql.Append(lastDay.ToShortDateString());
            strSql.Append("'");
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
        }


        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tcomplaint ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from tcomplaint T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            MySqlParameter[] parameters = {
                    new MySqlParameter("@tblName", MySqlDbType.VarChar, 255),
                    new MySqlParameter("@fldName", MySqlDbType.VarChar, 255),
                    new MySqlParameter("@PageSize", MySqlDbType.Int32),
                    new MySqlParameter("@PageIndex", MySqlDbType.Int32),
                    new MySqlParameter("@IsReCount", MySqlDbType.Bit),
                    new MySqlParameter("@OrderType", MySqlDbType.Bit),
                    new MySqlParameter("@strWhere", MySqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tcomplaint";
            parameters[1].Value = "id";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperMySQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}
